package work;

import com.mysql.jdbc.StringUtils;
import lombok.val;
import work.fram.*;
import work.person.helper.ConditionHelper;
import work.utils.TextUtils;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.*;

public class DeWeight {

    //TODO  使用身份证号进行去重操作
    public static String deWeight(String table) {

        try {


            Statement statement = JDBCConnect.getStatement();

            ResultSet resultSet = statement.executeQuery("SELECT sysid,name,idcard,create_time FROM " + table + " WHERE idcard IN (SELECT idcard FROM " + table + " GROUP BY idcard HAVING COUNT(*) > 1) ORDER BY idcard;");

            List<String> q = new ArrayList<>();

            List<String> sqls = new ArrayList<>();

            while (resultSet.next()) {

                //根据姓名和身份证号进行去重
                String name = resultSet.getString("name").trim();
                String idcard = resultSet.getString("idcard").trim();
                String id = resultSet.getString("sysid");
                String line = name + "-" + idcard + "-";

                if (q.contains(line)) {

                    AutoLogger.getINTERFACE().writeLine(table + "重复姓名和身份证号", name, idcard);
                    sqls.add("delete from " + table + " where sysid=\"" + id + "\";");
                    continue;
                }
                q.add(line);
            }

            StringBuilder builder = new StringBuilder();

            for (String sql : sqls) {
                //  statement.execute(sql);

                builder.append(sql).append("\n");

                //System.out.println(sql);

            }

            return builder.toString();
        } catch (Exception e) {
            return "";
        }
    }

    public static void main(String[] args) throws SQLException {

        SqlMaker maker = new SqlMaker();
        maker.setTable("tc_md_personnel");
        AutoLogger.getINTERFACE().writeLine("根据身份证号去重(保留最早记录)", "身份证号", "姓名", "被保留的sysid", "被删除的create_time", "被保留的create_time");

        StringBuilder builder = new StringBuilder();

        String table = "tc_md_personnel";
        String sql = "SELECT sysid,name,idcard,create_time FROM " + table + " WHERE idcard IN (SELECT idcard FROM " + table + " GROUP BY idcard HAVING COUNT(*) > 1) ORDER BY idcard;";

        System.out.println(sql);

        Map<String, List<Map<String, String>>> map = new HashMap<>();
        Statement statement = JDBCConnect.getStatement();
        ResultSet query = statement.executeQuery(sql);
        val split = "sysid,name,idcard,create_time".split(",");
        while (query.next()) {
            Map<String, String> m = new HashMap<>();

            for (String name : split) {
                String string = query.getString(name);
                if (string == null) {
                    m.put(name, "");
                } else {
                    m.put(name, string);
                }
            }

            String idcard = m.get("idcard");
            if (StringUtils.isNullOrEmpty(idcard)) {
                System.out.println("空白");
            } else {
                List<Map<String, String>> list = map.computeIfAbsent(idcard, k -> new ArrayList<>());
                list.add(m);
            }

        }

        map.forEach((ic, list) -> {

            //根据身份证号排序
            list.sort(Comparator.comparing(a -> a.get("idcard")));

            int len = list.size() - 1;
            Map<String, String> ret = list.get(list.size() - 1);
            for (int i = 0; i < len; i++) {
                Map<String, String> stringMap = list.get(i);
                if (maker.make(SqlMaker.TYPE_DELETE, ConditionHelper.create("sysid", "=", stringMap.get("sysid")))) {
                    builder.append(maker.getSql()).append("\n");
                }

                System.out.println("写入");
                AutoLogger.getINTERFACE().writeLine("根据身份证号去重(保留最早记录)", ic, stringMap.get("name"), ret.get("sysid"), stringMap.get("create_time"), ret.get("create_time"));
            }

        });

        TextUtils.textToClipboard(builder.toString());
        AutoLogger.getINTERFACE().close();
        AutoSqlWriter.INSTANCE.close();
    }


}
